--
-- Delete of a topic: update both items and child topics
--
-- First, reassign the items of the topic to the topics parent
-- But if there is no parent, raise an error and abort the update
-- The items must be reassigned somewhere else sensibly by hand.
--
-- Then update the children of the topic to now have their
-- grandparent (or NULL) as their parent.
-- 
CREATE OR REPLACE function del_topic()
RETURNS TRIGGER AS $$
DECLARE
   r_rec RECORD;
BEGIN
   FOR r_rec IN SELECT item_id, topic_id
                FROM item_topics
                WHERE topic_id = OLD.topic_id LOOP
      IF OLD.parent_id IS NULL THEN
         RAISE EXCEPTION
         'Cannot delete topic % until its records are reassigned.',
         OLD.topic_name;
      ELSE
         UPDATE item_topics SET topic_id = OLD.parent_id
         WHERE item_id = r_rec.item_id AND topic_id =  r_rec.topic_id;
      END IF;
   END LOOP;

   UPDATE topics SET parent_id=OLD.parent_id
   WHERE parent_id = OLD.topic_id;
   RETURN OLD;
END;
$$ language 'plpgsql';

CREATE TRIGGER del_topic BEFORE DELETE ON topics
   FOR EACH ROW EXECUTE PROCEDURE del_topic();


-- show all items and thair topics
CREATE VIEW it AS
SELECT i.id as i_id, i.name as item, it.topic_id, t.parent_id, t.name as topic
FROM    
        item_topics it JOIN items i on i.id = item_id JOIN topics t on t.id = topic_id;

--
-- Create an aggregation of topics to form the tree path for any topic
--
CREATE OR REPLACE FUNCTION get_topic_path( integer )
RETURNS TEXT AS $$
DECLARE
	path	text;
	topic_r	RECORD;
BEGIN
	SELECT INTO topic_r name, parent_id FROM topics WHERE topics.id = $1;
	path := topic_r.name;
	IF topic_r.parent_id IS NOT NULL
	THEN
		path := (SELECT get_topic_path(topic_r.parent_id)) || ' >> ' || path;
   END IF;
	RETURN path;
END;
$$ LANGUAGE 'plpgsql';

--
-- Re-Slice the previous query to be an ordered set of tuples
--
DROP TYPE topic_node CASCADE;
CREATE TYPE topic_node AS (tn_id integer, tn_parent integer);

CREATE or REPLACE FUNCTION get_topic_node( integer )
RETURNS SETOF topic_node AS $$
DECLARE
	t	topic_node;
	t2	topic_node;
BEGIN
	FOR t IN SELECT id, parent_id 
		FROM topics 
		WHERE id = $1
	LOOP
		IF t.tn_parent IS NOT NULL
		THEN
			FOR t2 IN SELECT * FROM get_topic_node(t.tn_parent) LOOP
				RETURN NEXT t2;
			END LOOP;
		END if;
		RETURN NEXT t;
	END LOOP;
	RETURN;
END;
$$ language 'plpgsql';


--
-- Expand the previous query to work by item.
-- This enables the previous query work w/ a parameter
-- by encasing it in yet another function.
-- It also expands to allow selection of multiple items.
-- Qualify the item in the call to the query using item_path
-- 
DROP TYPE item_path CASCADE;
CREATE TYPE item_path AS (item_id integer, topic_id integer);

CREATE OR REPLACE FUNCTION item_path ()
RETURNS SETOF item_path AS $$
DECLARE
	it	item_path;
	i_r	record;
	tn	topic_node;
BEGIN
	FOR i_r IN SELECT item_id, topic_id FROM item_topics LOOP
		it.item_id = i_r.item_id;
		FOR tn IN SELECT * FROM get_topic_node ( i_r.topic_id ) LOOP
			it.topic_id = tn.tn_id;
			RETURN NEXT it;
		END LOOP;
	END LOOP;
	RETURN;
END;
$$ LANGUAGE 'plpgsql';
